package com.lanyou.esb.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RestResource;

import com.lanyou.esb.entity.IfAlarm;
/**
 * 
 * @author ly-zengzugen
 *
 */
public interface IfAlarmRepository extends Repository<IfAlarm, String>, JpaSpecificationExecutor<IfAlarm> {
	
	
	@Query(nativeQuery = true, value = "SELECT t5.FIELD_NAME,t5.SYSTEM_NAME,t5.FIELD_ID,t5.FIELD_SYSTEM_ID,"
			+ " t1.IF_NAME,t1.ALARM_LEVEL,t1.ALARM_TYPE,t1.ERROR_DATE,"
			+ " t2.DEAL_DATE,t2.DEAL_PERSON,t2.ERROR_ANALYSIS "
			+ " FROM t_esb_bu_if_alarm_his t1 "
			+ " LEFT JOIN t_esb_bu_if_alarm_wo_his t2 "
			+ " ON t1.ASSIGN_WORK_CODE = t2.ASSIGN_WORK_CODE "
			+ " LEFT JOIN  "
			+ " (SELECT t3.SYSTEM_NAME,t4.FIELD_NAME,t3.FIELD_SYSTEM_ID,t4.FIELD_ID "
			+ " FROM t_cook_bu_field_system  t3 "
			+ " LEFT JOIN t_cook_db_field t4  "
			+ " ON t3.FIELD_ID = t4.FIELD_ID ) t5 "
			+ " ON t1.FIELD_SYSTEM_ID = t5.FIELD_SYSTEM_ID "
			+ " LEFT JOIN t_cook_bu_user_system t6 ON t6.FIELD_SYSTEM_ID = t1.FIELD_SYSTEM_ID"
			+ " WHERE ('' = :interfaceName OR t1.IF_NAME like :interfaceName)"
			+ " AND ('' = :fieldName OR t5.FIELD_NAME= :fieldName)"
			+ " AND ('' = :systemName OR t5.SYSTEM_NAME = :systemName)"
			+ " AND ('' = :alarmLevel OR t1.ALARM_LEVEL = :alarmLevel)"
			+ " AND ('' = :alarmType OR t1.ALARM_TYPE = :alarmType)"
			+ " AND ('' = :begainTime OR t2.DEAL_DATE > :begainTime )"
			+ " AND ('' = :endTime OR t2.DEAL_DATE <  :endTime)"
			+ " AND ('' = :userId OR t6.USER_ID = :userId)"
			+ " LIMIT :page1,:page2")
	
	List<Object[]> findAlarmHis(@Param("interfaceName") String interfaceName,
			@Param("fieldName") String fieldName,
			@Param("systemName") String systemName,
			@Param("alarmLevel") String alarmLevel,
			@Param("alarmType") String alarmType,
			@Param("begainTime") String begainTime,
			@Param("endTime") String endTime,
			@Param("userId") String userId,
			@Param("page1") int page1,
			@Param("page2") int page2);
	
	@Query(nativeQuery = true, value = "SELECT t5.FIELD_NAME,t5.SYSTEM_NAME,t5.FIELD_ID,t5.FIELD_SYSTEM_ID,"
			+ "t1.IF_NAME,t1.ALARM_LEVEL,t1.ALARM_TYPE,t1.ERROR_DATE,"
			+ "t2.DEAL_DATE,t2.DEAL_PERSON,t2.ERROR_ANALYSIS "
			+ "FROM t_esb_bu_if_alarm_his t1 "
			+ " LEFT JOIN t_esb_bu_if_alarm_wo_his t2 "
			+ " ON t1.ASSIGN_WORK_CODE = t2.ASSIGN_WORK_CODE "
			+ " LEFT JOIN  "
			+ " (SELECT t3.SYSTEM_NAME,t4.FIELD_NAME,t3.FIELD_SYSTEM_ID,t4.FIELD_ID "
			+ " FROM t_cook_bu_field_system  t3 "
			+ " LEFT JOIN t_cook_db_field t4  "
			+ " ON t3.FIELD_ID = t4.FIELD_ID ) t5 "
			+ " ON t1.FIELD_SYSTEM_ID = t5.FIELD_SYSTEM_ID "
			+ " LEFT JOIN t_cook_bu_user_system t6 ON t6.FIELD_SYSTEM_ID = t1.FIELD_SYSTEM_ID"
			+ " WHERE ('' = :interfaceName OR t1.IF_NAME like :interfaceName)"
			+ " AND ('' = :fieldName or t5.FIELD_NAME= :fieldName)"
			+ " AND ('' = :systemName OR t5.SYSTEM_NAME = :systemName)"
			+ " AND ('' = :alarmLevel OR t1.ALARM_LEVEL = :alarmLevel)"
			+ " AND ('' = :alarmType OR t1.ALARM_TYPE = :alarmType)"
			+ " AND ('' = :begainTime OR t2.DEAL_DATE > :begainTime )"
			+ " AND ('' = :endTime OR t2.DEAL_DATE <  :endTime)"
			+ " AND ('' = :userId OR t6.USER_ID = :userId)")
			
	
	List<Object[]> countAlarmHis(@Param("interfaceName") String interfaceName,
			@Param("fieldName") String fieldName,
			@Param("systemName") String systemName,
			@Param("alarmLevel") String alarmLevel,
			@Param("alarmType") String alarmType,
			@Param("begainTime") String begainTime,
			@Param("endTime") String endTime,
			@Param("userId") String userId);

	@Query(nativeQuery = true,
			value = "SELECT ts.FIELD_SYSTEM_ID, tf.FIELD_NAME, ts.SYSTEM_NAME, ta.IF_NAME, ta.IF_TYPE, SUM(ta.IF_ALARM_REPEAT)"
					+ " FROM t_cook_db_field tf, t_cook_bu_field_system ts, t_esb_bu_if_alarm ta"
					+ " WHERE tf.FIELD_ID = ts.FIELD_ID" + " AND ts.FIELD_SYSTEM_ID = ta.FIELD_SYSTEM_ID"
					+ " AND EXISTS(SELECT tus.FIELD_SYSTEM_ID FROM t_cook_bu_user_system tus WHERE tus.FIELD_SYSTEM_ID = ts.FIELD_SYSTEM_ID AND tus.USER_ID = :userId)"
					+ " GROUP BY tf.FIELD_NAME, ts.SYSTEM_NAME, ta.IF_NAME;")
	List<Object[]> findHttpErrorInterfaces(@Param("userId") String userId);
	
	//根据接口ID查询所有的告警信息
	long countByIfId(@Param("ifId") String ifId);
	
	long count();

	boolean exists(String id);

	@RestResource(exported = true)
	<S extends IfAlarm> S save(S entity); // could expose REST API

	<S extends IfAlarm> List<S> save(Iterable<S> entities);

	@RestResource(exported = true)
	// @RequiresPermissions(value={"InterfaceWarningInfoManagement:view","InterfaceWarningInfoManagement:maintain"},logical=Logical.OR)
	IfAlarm findOne(String id); // could expose REST API

	IfAlarm getOne(String id);

	// 以下三个接口只能选一个暴露为REST API

	@RestResource(exported = false)
	List<IfAlarm> findAll(); // could expose REST API

	@RestResource(exported = false)
	List<IfAlarm> findAll(Sort sort); // could expose REST API

	@RestResource(exported = true)
	// @RequiresPermissions(value={"InterfaceWarningInfoManagement:view","InterfaceWarningInfoManagement:maintain"},logical=Logical.OR)
	Page<IfAlarm> findAll(Pageable pageable); // could expose REST API

	List<IfAlarm> findAll(Iterable<String> ids);

	// 以下两个接口，要么都暴露为REST API，要么都不暴露
	@RestResource(exported = true)
	// @RequiresPermissions(value={"InterfaceWarningInfoManagement:maintain"})
	void delete(String id); // could expose REST API

	@RestResource(exported = true)
	// @RequiresPermissions(value={"InterfaceWarningInfoManagement:maintain"})
	void delete(IfAlarm entity); // could expose REST API

	void delete(Iterable<? extends IfAlarm> entities);

	void deleteAll();

	void flush();

	<S extends IfAlarm> S saveAndFlush(S entity);

	void deleteInBatch(Iterable<IfAlarm> entities);

	void deleteAllInBatch();

}